﻿CREATE PROCEDURE temp.s_Collection_CollectBy AS
DECLARE @LoanID int SET @LoanID = 1041947;
WITH N2 AS(
SELECT StatusDate,ROW_NUMBER()OVER(ORDER BY StatusDate DESC) Row FROM (
SELECT  TOP 2 StatusDate  FROM t_Note WHERE (LoanID = @LoanID) ORDER BY StatusDate DESC)T
)
SELECT N1.*,N2.* FROM
(
SELECT N.StatusDate,N.StatusUser,N.DateDueBy
FROM N2 INNER JOIN t_Note AS N ON N2.StatusDate = N.StatusDate
WHERE  N.LoanID = @LoanID AND N2.Row = 1
)N1 FULL JOIN
(
SELECT N.StatusDate,N.StatusUser,N.DateDueBy
FROM N2 INNER JOIN t_Note AS N ON N2.StatusDate = N.StatusDate
WHERE  N.LoanID = @LoanID AND N2.Row = 2
)N2 ON 1 = 1
